﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class RatedDAL
    {
        /// <summary>
        /// 链接数据库
        /// </summary>
        Database db = DatabaseFactory.CreateDatabase("constring");
        /// <summary>
        /// 显示评论
        /// </summary>
        /// <returns></returns>
        public List<Rated> ShowRated(int pageindex, int pagesize,string Ratedname)
        {
            try
            {
                if (pageindex < 1)
                {
                    pageindex = 1;
                }
                int start = (pageindex - 1) * pagesize + 1;
                int end = pageindex * pagesize;
                string sql = "select * from (select ROW_NUMBER() over(order by a.Rated_Id)i, a.Rated_Id, Rated_Content,Rated_Request,Login_Nickname,Articlescrap_Title from  Rated a inner join  Login_User b on a.Users_Id=b.Login_Id inner join Articlescrap c on a.Articlescrap_Id=c.Articlescrap_Id where DeleteId=1 ";
                if (Ratedname != null)
                {
                    sql += " and b.Login_Nickname like ('%" + Ratedname + "%') ";
                }

                sql+=" )s where i between " + start + " and " + end;
                DataSet ds = db.ExecuteDataSet(CommandType.Text,sql);
                List<Rated> list = new List<Rated>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Rated rate = new Rated();
                    rate.Rated_Id = (int)dr["Rated_Id"];
                    rate.Rated_Content = dr["Rated_Content"].ToString();
                    rate.Rated_Request = dr["Rated_Request"].ToString();
                    rate.Login_Nickname = dr["Login_Nickname"].ToString();
                    rate.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    list.Add(rate);
                }
                return list;
            }
            catch (Exception)
            {
                throw;
            }
            
        }
        public int CountRated(string Ratedname)
        {
            try
            {
                string sql = "select COUNT(*) from Rated a INNER JOIN Login_User b ON a.Users_Id=b.Login_Id where DeleteId=1 ";
                if(Ratedname!=null)
                {
                    sql += " AND b.Login_Nickname LIKE ('%" + Ratedname + "%') ";
                }
                int dr = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, sql));
                return dr;
            }
            catch(Exception)
            {
                throw;
            }
        }
        /// <summary>
        /// 删除评论信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelRated( int id)
        {
            try
            {
                int dr = Convert.ToInt32(db.ExecuteNonQuery(CommandType.Text, "update Rated set DeleteId=0 where Rated_Id=" + id));
                return dr;
            }
            catch (Exception)
            {
                throw;
            }
            
        }
    }
}
